Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# TO_TIMESTAMP_TZ

Syntax

Description of the illustration to_timestamp_tz.gif

Purpose

`TO_TIMESTAMP_TZ` converts `char` of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` datatype to a value of `TIMESTAMP` `WITH` `TIME` `ZONE` datatype.

Note: This function does not convert character strings to `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE`. To do this, use a `CAST` function, as shown in CAST.

The optional `fmt` specifies the format of `char`. If you omit `fmt`, then `char` must be in the default format of the `TIMESTAMP` `WITH` `TIME` `ZONE` datatype. The optional `'nlsparam'` has the same purpose in this function as in the `TO_CHAR` function for date conversion.

Examples

The following example converts a character string to a value of `TIMESTAMP` `WITH` `TIME` `ZONE`:

    SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00',
       'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

    TO_TIMESTAMP_TZ('1999-12-0111:00:00-08:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
    --------------------------------------------------------------------
    01-DEC-99 11.00.00.000000000 AM -08:00

The following example casts a null column in a `UNION` operation as `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` using the sample tables `oe.order_items` and `oe.orders`:

    SELECT order_id, line_item_id,
       CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) order_date
       FROM order_items
    UNION
    SELECT order_id, to_number(null), order_date
       FROM orders;

      ORDER_ID LINE_ITEM_ID ORDER_DATE
    ---------- ------------ -----------------------------------
          2354            1
          2354            2
          2354            3
          2354            4
          2354            5
          2354            6
          2354            7
          2354            8
          2354            9
          2354           10
          2354           11
          2354           12
          2354           13
          2354              14-JUL-00 05.18.23.234567 PM
          2355            1
          2355            2
    ...